# coding: utf-8

from dbutils.pooled_db import PooledDB
from pymysql.cursors import DictCursor


class SQLPool(object):
    """
    MYSQL数据库对象，负责产生数据库连接 , 此类中的连接采用连接池实现获取连接对象：conn = Mysql.getConn()
            释放连接对象;conn.close()或del conn
    """
    _instances = {}

    def __new__(cls, conn_name: str = None, *args, **kwargs):
        if conn_name not in cls._instances:
            cls._instances[conn_name] = super().__new__(cls)
        return cls._instances[conn_name]

    def init_app(self, app=None):
        if self._pool is None:
            self._pool = self._init_pool(app)

    def _init_pool(self, app):
        config = app.config.copy()
        config = self._set_default(config)
        return PooledDB(creator=config['DB_CREATOR'], mincached=config['DB_MIN_CACHED'],
                        maxcached=config['DB_MAX_CACHED'], host=config['DB_HOST'],
                        port=config['DB_PORT'], user=config['DB_USER'], passwd=config['DB_PASSWORD'],
                        db=config['DB_NAME'], use_unicode=config['DB_UNICODE'], charset=config['DB_CHARSET'],
                        cursorclass=DictCursor, autocommit=False)

    @staticmethod
    def _set_default(config):
        config.setdefault('DB_HOST', '127.0.0.1')
        config.setdefault('DB_PORT', 3306)
        config.setdefault('DB_USER', None)
        config.setdefault('DB_PASSWORD', None)
        config.setdefault('DB_NAME', None)
        # 使用连接数据库的模块
        config.setdefault('DB_CREATOR', None)
        config.setdefault('DB_UNICODE', True)
        config.setdefault('DB_CHARSET', 'utf8')  # 数据库连接编码
        config.setdefault('DB_MIN_CACHED', 2)  # 启动时开启的闲置连接数量(缺省值 0 开始时不创建连接)
        config.setdefault('DB_MAX_CACHED', 5)  # 连接池中允许的闲置的最多连接数量(缺省值 0 代表不闲置连接池大小)
        # 共享连接数允许的最大数量(缺省值 0 代表所有连接都是专用的)如果达到了最大数量, 被请求为共享的连接将会被共享使用
        # 此值无效， 因为pymsql的threadsafty = 1
        config.setdefault('DB_MAX_SHARED', 20)  #
        config.setdefault('DB_MAX_CONNECTIONS', 100)  # 创建连接池的最大数量(缺省值 0 代表不限制)
        # 设置在连接池达到最大数量时的行为(缺省值 0 或 False 代表返回一个错误<toMany......> 其他代表阻塞直到连接数减少,连接被分配)
        config.setdefault('DB_BLOCKING', True)
        # 单个连接的最大允许复用次数(缺省值 0 或 False 代表不限制的复用).当达到最大数时,连接会自动重新连接(关闭和重新打开)
        config.setdefault('DB_MAX_USAGE', 0)
        # 一个可选的SQL命令列表用于准备每个会话，如["set datestyle to german", ...]
        config.setdefault('DB_SET_SESSION', None)
        return config

    def from_object(self, obj):
        if self._pool is not None:
            return
        config = dict()
        for key in dir(obj):
            if key.isupper():
                config[key] = getattr(obj, key)
        config = self._set_default(config)
        self._pool = PooledDB(creator=config['DB_CREATOR'], mincached=config['DB_MIN_CACHED'],
                              maxcached=config['DB_MAX_CACHED'], host=config['DB_HOST'],
                              port=config['DB_PORT'], user=config['DB_USER'], passwd=config['DB_PASSWORD'],
                              db=config['DB_NAME'], use_unicode=config['DB_UNICODE'], charset=config['DB_CHARSET'],
                              cursorclass=DictCursor, autocommit=False)

    def __init__(self, conn_name: str = None):
        self._name = conn_name
        self._pool = None
        self._conn = None
        self._cursor = None

    def __enter__(self):
        self._conn = self._pool.connection()
        self._cursor = self._conn.cursor()

    def __exit__(self, exc_type, exc_val, exc_tb):
        self._cursor.close()
        self._conn.close()

    @property
    def conn(self):
        return self._conn

    @property
    def connect_name(self):
        return self._name

    def __del__(self):
        self.close()

    def close(self):
        try:
            if self._cursor is not None:
                self._cursor.close()
            if self.conn is not None:
                self.conn.close()
        except Exception as e:
            print(str(e))

    def nextset(self):
        return self._cursor.nextset()

    def mogrify(self, query, args=None):
        return self._cursor.mogrify(query, args)

    def execute(self, query, args=None):
        return self._cursor.execute(query, args)

    def executemany(self, query, args):
        return self._cursor.executemany(query, args)

    def callproc(self, procname, args=()):
        return self._cursor.callproc(procname, args)

    def fetchone(self):
        return self._cursor.fetchone()

    def fetchmany(self, size=None):
        return self._cursor.fetchmany(size)

    def fetchall(self):
        return self._cursor.fetchall()

    def scroll(self, value, mode='relative'):
        return self._cursor.scroll(self, value, mode)

    def commit(self):
        return self._conn.commit()

    def rollback(self):
        return self._conn.rollback()
